from datetime import date
from logging import exception
from PyQt5.QtCore import QDateTime, QTime,Qt
import xlrd
import PyQt5
import datetime
from log import logger
class infoXlsxReader():
    def __init__(self,filename):
        self.filename = filename
        self.data = xlrd.open_workbook(self.filename)
        self.table = self.data.sheets()[0]

    #读取表中所有数据
    def readAllInfo(self):
        nrows = self.table.nrows
        lists = []
        for i in range(2,nrows):
            row = []
            mapList = self.table.row(i)
            for j in range(len(mapList)):
                value = None
                if j == 0:
                    try:
                        value = str(self.float2Int(mapList[j].value))#将ID号转化为字符串
                    except Exception:
                        value = str(mapList[j].value)
                elif j == 2:
                    value = str(self.float2Date(mapList[j].value))#将日期格式转化为字符串
                else:                
                    value = str(mapList[j].value)
                row.append(value)
            lists.append(row)
        return lists
   
    #转换日期戳
    def float2Date(self,dates):#定义转化日期戳的函数,dates为日期戳
        if dates == '':
            return
        dates = float(dates)
        delta=datetime.timedelta(days=dates)
        today=datetime.datetime.strptime('1899-12-30','%Y-%m-%d')+delta#将1899-12-30转化为可以计算的时间格式并加上要转化的日期戳
        return datetime.datetime.strftime(today,'%Y-%m-%d')#制定输出日期的格式        
   
    #将ID号转化为整形
    def float2Int(self,id):
        return str(int(float(id)))


# reader = infoXlsxReader(r'C:\Users\75684\Desktop\curriculum design2\课程设计检验数据.xlsx')
# infoLists = reader.readAllInfo()
# print(infoLists)

